﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using bdxyit.com.RecruitmentAppSys.IDAL;
using bdxyit.com.RecruitmentAppSys.Models;
using System.Data.SqlClient;
using System.Data;
using bdxyit.com.RecruitmentAppSys.DBUtility;

/***
*机器名：BDXYIT-HUI
*项目名称：$projectname$
*命名空间：bdxyit.com.RecruitmentAppSys.SQLDAL
*文件名：ApplyJobActionModels
*系统时间：3/25/2012 3:32:27 PM
*当前用户：huihui
*创建时间：2012
*
*修改时间：2012 3/25/2012 3:32:27 PM
*修改事件：
***/

namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class ApplyJobActionDAL : IApplyJobActionDAL
    {
        private const string SQL_SELECT_APPLYJOBACTION_BY_USERID = " select  UserID,PostID,CompanyID,ActionID,Flag from ApplyJobActions "
                                                                + "  where UserID=@UserID";
        private const string SQL_DELETE_APPLYJOBACTION_BY_USERID = " delete from ApplyJobActions "
                                                                 + "  where UserID=@UserID";
        private const string SQL_UPDATE_APPLYJOBACTION_BY_USERID = " update ApplyJobActions set "
                                                                + " UserID=@UserID,"
                                                                + " PostID=@PostID,"
                                                                + " CompanyID=@CompanyID,"
                                                                + " ActionID=@ActionID,"
                                                                + " Flag=@Flag"
                                                                + "  where UserID=@UserID";
        private const string SQL_SELECT_APPLYJOBACTION_BY_USERID_AND_FLAG = " select   UserID,PostID,CompanyID,ActionID,Flag from ApplyJobActions "
                                                                          + "  where UserID=@UserID and Flag=@Flag";
        private const string SQL_DELETE_APPLYJOBACTION_BY_USERID_AND_FLAG = " delete from ApplyJobActions " 
                                                                            + "  where UserID=@UserID and Flag=@Flag";
        private const string SQL_INSERT_APPLYJOBACTION = " insert into ApplyJobActions("
                                                        + " UserID,PostID,CompanyID,ActionID,Flag)"
                                                        + "  values ("
                                                        + " @UserID,@PostID,@CompanyID,@ActionID,@Flag)";
        private const string FIELD_USERID = "@UserID";
        private const string FIELD_POSTID = "@PostID";
        private const string FIELD_COMPANYID = "@CompanyID";
        private const string FIELD_ACTIONID = "@ActionID";
        private const string FIELD_FALG = "@Flag";

        /// <summary>
        /// 获取应聘职位
        /// </summary>
        /// <returns></returns>
        public List<ApplyJobAction> SelectApplyJobActionByUserId(string userId)
        {
            List<ApplyJobAction> aja = new List<ApplyJobAction>();

            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_USERID, SqlDbType.VarChar,128);
            parm.Value = userId;
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_APPLYJOBACTION_BY_USERID,parm))
            {
                while (rdr.Read())
                {
                    ApplyJobAction aj = new ApplyJobAction();
                    if (rdr["UserID"] != null && rdr["UserID"].ToString() != "")
                    {
                        aj.UserID =  rdr["UserID"].ToString() ;
                    }
                    if (rdr["PostID"] != null && rdr["PostID"].ToString() != "")
                    {
                        aj.PostID = rdr["PostID"].ToString();
                    }
                    if (rdr["CompanyID"] != null && rdr["CompanyID"].ToString() != "")
                    {
                        aj.CompanyID = rdr["CompanyID"].ToString();
                    }
                    if (rdr["ActionID"] != null && rdr["ActionID"].ToString() != "")
                    {
                        aj.ActionID = int.Parse(rdr["ActionID"].ToString());
                    }
                    if (rdr["Flag"] != null && rdr["Flag"].ToString() != "")
                    {
                        aj.Flag = int.Parse(rdr["Flag"].ToString());
                    }
                    aja.Add(aj);
                }
            }
            return aja;
        }
         /// <summary>
        /// 获取应聘职位
        /// </summary>
        /// <returns></returns>
        public ApplyJobAction SelectApplyJobActionByUserIdAndFlag(string userId,int flag)
        {
            ApplyJobAction aja = null;
            //创建参数
            SqlParameter[] parm = new SqlParameter[] { new SqlParameter(FIELD_USERID, SqlDbType.VarChar, 128), new SqlParameter(FIELD_FALG,SqlDbType.Int) }; 
            parm[0].Value = userId;
            parm[1].Value = flag;
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_APPLYJOBACTION_BY_USERID_AND_FLAG,parm))
            {
                if (rdr.Read())
                {
                    aja = new ApplyJobAction();
                    if (rdr["UserID"] != null && rdr["UserID"].ToString() != "")
                    {
                        aja.UserID = rdr["UserID"].ToString();
                    }
                    if (rdr["PostID"] != null && rdr["PostID"].ToString() != "")
                    {
                        aja.PostID = rdr["PostID"].ToString();
                    }
                    if (rdr["CompanyID"] != null && rdr["CompanyID"].ToString() != "")
                    {
                        aja.CompanyID = rdr["CompanyID"].ToString();
                    }
                    if (rdr["ActionID"] != null && rdr["ActionID"].ToString() != "")
                    {
                        aja.ActionID = int.Parse(rdr["ActionID"].ToString());
                    }
                    if (rdr["Flag"] != null && rdr["Flag"].ToString() != "")
                    {
                        aja.Flag = int.Parse(rdr["Flag"].ToString());
                    }
                }
            }
            return aja;
        }

        /// <summary>
        /// 删除应聘职位
        /// </summary>
        /// <returns></returns>
        public bool DeleteApplyJobActionByUserId(string userId)
        {
            bool flag = false;
            SqlParameter parm = new SqlParameter(FIELD_USERID, SqlDbType.VarChar, 128);
            parm.Value = userId;
            SqlCommand cmd = new SqlCommand();
            using(SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_APPLYJOBACTION_BY_USERID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="flagA"></param>
        /// <returns></returns>
        public bool DeleteApplyJobActionByUserIdAndFlag(string userId, int flagA)
        {
            bool flag = false;
            SqlParameter[] parm = new SqlParameter[] { new SqlParameter(FIELD_USERID, SqlDbType.VarChar, 128), new SqlParameter(FIELD_FALG, SqlDbType.Int) };
            parm[0].Value = userId;
            parm[1].Value = flagA;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_APPLYJOBACTION_BY_USERID_AND_FLAG;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }


        /// <summary>
        /// 插入应聘职位
        /// </summary>
        /// <returns></returns>
        public bool InsertApplyJobAction(ApplyJobAction aja) 
        {
            bool flag = false;
            SqlParameter[] parm = new SqlParameter[] { new SqlParameter(FIELD_USERID, SqlDbType.VarChar, 50), new SqlParameter(FIELD_POSTID, SqlDbType.VarChar, 10), new SqlParameter(FIELD_COMPANYID, SqlDbType.VarChar, 10), new SqlParameter(FIELD_ACTIONID, SqlDbType.VarChar, 10), new SqlParameter(FIELD_FALG, SqlDbType.VarChar, 10) };
            parm[0].Value = aja.UserID;
            parm[1].Value = aja.PostID;
            parm[2].Value = aja.CompanyID;
            parm[3].Value = aja.ActionID;
            parm[4].Value = aja.Flag;
            SqlCommand cmd = new SqlCommand();
            foreach (SqlParameter s in parm)
            {
                cmd.Parameters.Add(s);
            }
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_APPLYJOBACTION_BY_USERID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }


        /// <summary>
        /// 更新应聘职位
        /// </summary>
        /// <returns></returns>
        public bool UpdateApplyJobAction(ApplyJobAction aja)
        {
            bool flag = false;
            SqlParameter[] parm = new SqlParameter[] { new SqlParameter(FIELD_USERID, SqlDbType.VarChar, 50), new SqlParameter(FIELD_POSTID, SqlDbType.VarChar, 10), new SqlParameter(FIELD_COMPANYID, SqlDbType.VarChar, 10), new SqlParameter(FIELD_ACTIONID, SqlDbType.VarChar, 10), new SqlParameter(FIELD_FALG, SqlDbType.VarChar, 10) };
            parm[0].Value = aja.UserID;
            parm[1].Value = aja.PostID;
            parm[2].Value = aja.CompanyID;
            parm[3].Value = aja.ActionID;
            parm[4].Value = aja.Flag;
            SqlCommand cmd = new SqlCommand();
            foreach (SqlParameter s in parm)
            {
                cmd.Parameters.Add(s);
            }
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_APPLYJOBACTION_BY_USERID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
    }
}